package br.fatec.ads.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.fatec.ads.connection.DataSource;
import br.fatec.ads.exception.DataBaseException;
import br.fatec.ads.exception.RedeSocialException;
import br.fatec.ads.model.RedeSocial;

/**
 * Classe intermediaria entre o modelo e o banco de dados 
 */
public class RedeSocialDAO {
	private static RedeSocialDAO reseSocialDAO = null;
	private static String INCLUIR_REDE_SOCIAL = "EXEC P_REDE_SOCIAL_INCLUIR ?, ?";
	private static String ALTERAR_REDE_SOCIAL = "EXEC P_REDE_SOCIAL_ALTERAR ?, ?, ?";
	private static String EXCLUIR_REDE_SOCIAL = "EXEC P_REDE_SOCIAL_EXCLUIR ?, ?";
	private static String CONSULTAR_REDE_SOCIAL = "EXEC P_REDE_SOCIAL_CONSULTAR ?";
	private static String LISTAR_REDE_SOCIAL = "EXEC P_REDE_SOCIAL_LISTAR ?";
	
	//Construtor Padrao
	public RedeSocialDAO() {
		super();
	}
	
	public static RedeSocialDAO getInstance(){
		if(reseSocialDAO == null){
			reseSocialDAO = new RedeSocialDAO();
		}
		
		return reseSocialDAO;
	}
	
    public void incluirRedeSocial(RedeSocial redeSocial) throws RedeSocialException, DataBaseException{
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(INCLUIR_REDE_SOCIAL);

                int i = 0;
                query.setString(++i, redeSocial.getDescRedeSocial());
                query.setString(++i, redeSocial.getUsuario());

                query.executeUpdate();
            }catch(SQLException cause){
                throw new RedeSocialException("Problemas ao INSERIR a Rede Social!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        } catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }
    
    public void alterarRedeSocial(RedeSocial redeSocial) throws RedeSocialException, DataBaseException {
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(ALTERAR_REDE_SOCIAL);

                int i = 0;
                query.setInt(++i, redeSocial.getIdRedeSocial());
                query.setString(++i, redeSocial.getDescRedeSocial());
                query.setString(++i, redeSocial.getUsuario());

                query.executeUpdate();
            }catch(SQLException cause){
                throw new RedeSocialException("Problemas ao ALTERAR a Rede Social!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        }catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }
    
    public void excluirRedeSocial(RedeSocial redeSocial) throws RedeSocialException, DataBaseException{
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(EXCLUIR_REDE_SOCIAL);

                int i = 0;
                query.setInt(++i, redeSocial.getIdRedeSocial());
                query.setString(++i, redeSocial.getUsuario());

                query.executeUpdate();
            }catch(SQLException cause){
                throw new RedeSocialException("Problemas ao EXCLUIR a Rede Social!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        }catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }
    
    public RedeSocial getRedeSocial(int idRedeSocial) throws RedeSocialException, DataBaseException{
    	Connection connection = null;
    	PreparedStatement query = null;
    	ResultSet resultSet = null;
    	RedeSocial redeSocial = null;
    	
    	try{
    		try{
    			redeSocial = new RedeSocial();
    			
    			connection = DataSource.getConnection();
    			query = connection.prepareStatement(CONSULTAR_REDE_SOCIAL);
    			
    			int i = 0;
    			query.setInt(++i, idRedeSocial);
    			
    			resultSet = query.executeQuery();
    			
    			while(resultSet.next()){
    				redeSocial.setIdRedeSocial(resultSet.getInt("IDREDESOCIAL"));
    				redeSocial.setDescRedeSocial(resultSet.getString("DESCREDESOCIAL"));
    				redeSocial.setAtivo(resultSet.getString("ATIVO"));
    				redeSocial.setDataHora(resultSet.getTimestamp("DTHRINCLUSAO"));
    				redeSocial.setUsuario(resultSet.getString("USUARIOINCLUSAO"));
    			}
    		}catch (SQLException cause) {
    			throw new RedeSocialException("Problemas ao CONSULTAR a Rede Social!", cause);
			}finally{
				DataSource.close(connection);
				DataSource.close(query);
				DataSource.close(resultSet);
			}
    	}catch (DataBaseException cause) {
    		throw new DataBaseException(cause);
		}
    	
    	return redeSocial;
    }
    
    public List listarRedeSocial(RedeSocial redeSocial) throws RedeSocialException, DataBaseException{
    	Connection connection = null;
    	PreparedStatement query = null;
    	ResultSet resultSet = null;
    	List listaRedeSocial = new ArrayList();
    	
    	try{
    		try{
    			connection = DataSource.getConnection();
    			query = connection.prepareStatement(LISTAR_REDE_SOCIAL);
    			
    			int i = 0;
    			query.setInt(++i, redeSocial.getIdRedeSocial());
    			
    			resultSet = query.executeQuery();
    			
    			while(resultSet.next()){
    				RedeSocial RedeSoc = new RedeSocial();
    				
    				RedeSoc.setIdRedeSocial(resultSet.getInt("IDREDESOCIAL"));
    				RedeSoc.setDescRedeSocial(resultSet.getString("DESCREDESOCIAL"));
    				RedeSoc.setAtivo(resultSet.getString("ATIVO"));
    				RedeSoc.setDataHora(resultSet.getTimestamp("DTHRINCLUSAO"));
    				RedeSoc.setUsuario(resultSet.getString("USUARIOINCLUSAO"));
    				
    				listaRedeSocial.add(RedeSoc);
    			}
    		}catch (SQLException cause) {
    			throw new RedeSocialException("Problemas ao LISTAR a Rede Social!", cause);
			}finally{
				DataSource.close(connection);
				DataSource.close(query);
				DataSource.close(resultSet);
			}
    	}catch (DataBaseException cause) {
    		throw new DataBaseException(cause);
		}
    	
    	return listaRedeSocial;
    }
}